package dao.impl;

import dao.ProjectdeclarationDao;
import model.Projectdeclaration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProjectdeclarationDaoImpl implements ProjectdeclarationDao {
    /**
     * The Pstmt.
     */
    PreparedStatement pstmt = null;
    /**
     * The Rs.
     */
    ResultSet rs = null;


    @Override
    public int insert(Projectdeclaration projectdeclaration) {
        int ret = 0;
        String sql = "insert into projectdeclaration values(null,?,?,?,?,?)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, projectdeclaration.getDesc());
            pstmt.setLong(2, projectdeclaration.getPassbyc());
            pstmt.setLong(3, projectdeclaration.getTid());
            pstmt.setString(4, projectdeclaration.getTdesc());
            pstmt.setLong(5, projectdeclaration.getPassbyc());
            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public int update(Projectdeclaration projectdeclaration) {
        int ret = 0;
        String sql = "update projectdeclaration set `desc`=?,passbyt=?,tdesc=?,passbyt=? where tid=?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, projectdeclaration.getDesc());
            pstmt.setLong(2, projectdeclaration.getPassbyt());
            pstmt.setString(3, projectdeclaration.getTdesc());
            pstmt.setLong(4, projectdeclaration.getPassbyc());
            pstmt.setLong(5, projectdeclaration.getTid());


            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public List<Projectdeclaration> selectByPassbyCid(int cid, int pageSize, int pageIndex) {
        List<Projectdeclaration> list = new ArrayList<Projectdeclaration>();
        String sql = "select `desc`,passbyt,tid,tdesc,passbyt from projectdeclaration,company where cid=?limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, cid);

            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, pageSize * (pageIndex - 1));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(new Projectdeclaration(rs.getString("desc"), rs.getLong("passbyt"), rs.getLong("tid"), rs.getString("tdesc"), rs.getLong("passbyt")));
            }
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露"
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
    public int deleteBytid(int tid) {
        int ret = 0;
        String sql = "delete from projectdeclaration where tid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,tid);
            ret = pstmt.executeUpdate();
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return ret;
    }

    @Override
    public List<Projectdeclaration> selectByPdid(int pdid, int pageSize, int pageIndex) {
        List<Projectdeclaration> list = new ArrayList<Projectdeclaration>();
        String sql = "select * from projectdeclaration where pdid = ?limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pdid);

            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, pageSize * (pageIndex - 1));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(new Projectdeclaration(rs.getString("desc"), rs.getLong("passbyt"), rs.getLong("tid"), rs.getString("tdesc"), rs.getLong("passbyt")));
            }
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露"
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;
    }

    @Override
//    public List<Projectdeclaration> selectByPassbyTid(int tid, int pageSize, int pageIndex) {
//        List<Projectdeclaration> list = new ArrayList<Projectdeclaration>();
//        String sql = "select * from projectdeclaration where tid = ?limit ? offset ?";
//        Connection conn = BaseDao.getConnection();
//        try {
//            conn = BaseDao.getConnection();
//            pstmt = conn.prepareStatement(sql);
//            pstmt.setInt(1, tid);
//
//            pstmt.setInt(2, pageSize);
//            pstmt.setInt(3, pageSize * (pageIndex - 1));
//
//            rs = pstmt.executeQuery();
//            while (rs.next()) {
//                list.add(new Projectdeclaration(rs.getString("desc"), rs.getLong("passbyt"), rs.getLong("tid"), rs.getString("tdesc"), rs.getLong("passbyt")));
//            }
//            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露"
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            BaseDao.close(rs, pstmt, conn);
//        }
//        return list;
//
//    }
    public List<Projectdeclaration> selectByPassbyTid(int tid, int pageSize, int pageIndex){
        List<Projectdeclaration> list = new ArrayList<Projectdeclaration>();
        String sql = "select * from projectdeclaration where tid in (select tid from team where smid=?) limit ? offset ?";
        Connection conn = BaseDao.getConnection();
        try {
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, tid);

            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, pageSize * (pageIndex - 1));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                list.add(new Projectdeclaration(rs.getString("desc"), rs.getLong("passbyt"), rs.getLong("tid"), rs.getString("tdesc"), rs.getLong("passbyt")));
            }
            //此处在这里不应该在这里调用close方法,否则可能会造成资源泄露"
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return list;

    }


}
